﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using MySql.Data;
using OnlineCooperation.Entity;

namespace OnlineCooperation.DB
{
    public class TeamDB
    {
        String strConn;
        datasetMgn DSMgn;

        //构造函数， 为strConn赋值
        public TeamDB()
        {
            strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            DSMgn = new datasetMgn();
        }

        /// <summary>
        /// 向数据库中插入一条团队记录，完成创建团队
        /// </summary>
        /// <param name="team">团队，其ID字段初始为无效字段</param>
        /// <returns>创建的状态：返回1成功，存在同名团队返回0失败</returns>
        public int Insert(Team team)
        {
            //团队名判重
            String strSql = "SELECT * FROM `teaminfo` WHERE `TeamName`=\'" + team.Name + "\'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count != 0)
                return 0;                                                                                   //存在同名团队，注册失败
            
            //不存在同名用户，可以进行创建
            strSql = "INSERT INTO `teaminfo`(`TeamName`,`Description`,`AdminID`) VALUES(?TeamName0, ?Description0, ?AdminID0)";
            MySqlParameter[] parameters = new MySqlParameter[3];
            parameters[0] = new MySqlParameter("?TeamName0", MySqlDbType.VarChar);
            parameters[0].Value = team.Name;
            parameters[1] = new MySqlParameter("?Description0", MySqlDbType.VarChar);
            parameters[1].Value = team.Description;
            parameters[2] = new MySqlParameter("?AdminID0", MySqlDbType.Int32);
            parameters[2].Value = team.AdminID;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);

            strSql = "SELECT `TeamID` FROM `teaminfo` WHERE `TeamName`=\'" + team.Name + "\'";
            String CurrentTeamID = DSMgn.Readstr(strSql, 0, strConn);

            //修改reluserteam，记录该创建者与团队的对应关系
            strSql = "INSERT INTO `reluserteam`(`UserID`,`TeamID`) VALUES(?UserID3, ?TeamID3)";
            MySqlParameter[] parameters1 = new MySqlParameter[2];
            parameters1[0] = new MySqlParameter("?UserID3", MySqlDbType.Int32);
            parameters1[0].Value = team.AdminID;
            parameters1[1] = new MySqlParameter("TeamID3", MySqlDbType.Int32);
            parameters1[1].Value = Convert.ToInt32(CurrentTeamID);
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters1);

            //在团队内发布新鲜事，表明该团队已创建
            strSql = "SELECT `UserName` FROM `userinfo` WHERE `UserID` ='" + team.AdminID + "'";
            String AdminName = DSMgn.Readstr(strSql, 0, strConn);
            strSql = "SELECT max(`PostID`) FROM `post`";
            String CurrentMaxPostID = DSMgn.Readstr(strSql, 0, strConn);
            if (CurrentMaxPostID == "")
                CurrentMaxPostID = "-1";
            strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2, ?PDateAndTime2)";
            MySqlParameter[] parameters2 = new MySqlParameter[6];
            parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
            parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
            parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
            parameters2[1].Value = 0;//所有新鲜事的StateID为0
            parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
            parameters2[2].Value = "用户" + AdminName + "创建了团队" + team.Name + "撒花~~~";
            parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
            parameters2[3].Value = 0;
            parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
            parameters2[4].Value = Convert.ToInt32(CurrentTeamID);
            parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
            parameters2[5].Value = DateTime.Now;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);

            return 1;                                                                                   //注册成功
        }

        /// <summary>
        /// 在数据库中修改团队信息
        /// </summary>
        /// <param name="team">团队，除了ID和AdminID以外，信息已更新</param>
        /// <returns>修改结果：若存在同名团队，则修改失败返回0；否则修改成功返回1</returns>
        public int Update(Team team)	
        {
            //获取原来的团队名称、团队描述
            String strSql = "SELECT `TeamName`, `Description` FROM `teaminfo` WHERE `TeamID`='" + team.ID + "'";
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            String PreviousName = DSMgn.Readstr(strSql, 0, strConn);
            String PreviousDes = DSMgn.Readstr(strSql, 1, strConn);

            //团队名称判重
            strSql = "SELECT `TeamID` FROM `teaminfo` WHERE `TeamName`=\'" + team.Name + "\'";
            DataRow dr = MySqlHelper.ExecuteDataRow(strConn, strSql, null);
            String PreviousID;
            if (dr != null)
            {
                PreviousID = dr[0].ToString();
                if (Convert.ToInt32(PreviousID) != team.ID)
                    return 0;                                                                               //存在同名不同ID团队，修改失败
            }

            strSql = "UPDATE `teaminfo` SET `TeamName` = ?TeamName0, `Description` = ?Description0, `AdminID` = ?AdminID0 WHERE `TeamID`='" + team.ID + "'";
            MySqlParameter[] parameters = new MySqlParameter[3];
            parameters[0] = new MySqlParameter("?TeamName0", MySqlDbType.VarChar);
            parameters[0].Value = team.Name;
            parameters[1] = new MySqlParameter("?Description0", MySqlDbType.VarChar);
            parameters[1].Value = team.Description;
            parameters[2] = new MySqlParameter("?AdminID0", MySqlDbType.Int32);
            parameters[2].Value = team.AdminID;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters);

            //若该团队修改了团队名/描述，则在团队内发布新鲜事，表明该修改
            if (PreviousName == team.Name && PreviousDes == team.Description)
                return 1;                                                                                       //修改成功，实际上未做任何修改

            strSql = "SELECT max(`PostID`) FROM `post`";
            dr = MySqlHelper.ExecuteDataRow(strConn, strSql, null);
            String CurrentMaxPostID;
            if (dr == null)
                CurrentMaxPostID = "-1";
            else
                CurrentMaxPostID = dr[0].ToString();

            strSql = "INSERT INTO `post`(`PostID`,`StateID`,`PostContent`,`TopicID`,`PostTeamID`,`PDateAndTime`) VALUES(?PostID2, ?StateID2, ?PostContent2, ?TopicID2, ?PostTeamID2,?PDateAndTime2)";
            MySqlParameter[] parameters2 = new MySqlParameter[6];
            parameters2[0] = new MySqlParameter("?PostID2", MySqlDbType.Int32);
            parameters2[0].Value = Convert.ToInt32(CurrentMaxPostID) + 1;
            parameters2[1] = new MySqlParameter("?StateID2", MySqlDbType.Int32);
            parameters2[1].Value = 0;                                                              //所有新鲜事的StateID为0
            parameters2[2] = new MySqlParameter("?PostContent2", MySqlDbType.VarChar);
            parameters2[2].Value = "";
            if (PreviousName != team.Name)
                parameters2[2].Value += "我们的团队已更名为" + team.Name + "啦~";
            if (PreviousDes != team.Description)
                parameters2[2].Value += "团队描述已修改为" + team.Description + "哦~";
            parameters2[3] = new MySqlParameter("?TopicID2", MySqlDbType.Int32);
            parameters2[3].Value = 0;
            parameters2[4] = new MySqlParameter("?PostTeamID2", MySqlDbType.Int32);
            parameters2[4].Value = team.ID;
            parameters2[5] = new MySqlParameter("?PDateAndTime2", MySqlDbType.DateTime);
            parameters2[5].Value = DateTime.Now;
            MySqlHelper.ExecuteNonQuery(strConn, strSql, parameters2);

            return 1;                                                                                       //修改成功
        }

        /// <summary>
        /// 在数据库中注销该团队
        /// </summary>
        /// <param name="teamid">团队编号</param>
        /// <returns>删除结果：删除相应的团队记录，团队文件记录，团队新鲜事/话题记录，团队成员关系，返回1成功，0失败</returns>
        public int Delete(int teamid)
        {
            String strSql = "DELETE  FROM `teaminfo` WHERE `TeamID`='" + teamid + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除reluserteam中的相应记录
            strSql = "DELETE  FROM `reluserteam` WHERE `TeamID`='" + teamid + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除teamfile中的相应记录
            strSql = "DELETE  FROM `teamfile` WHERE `TeamID`='" + teamid + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除post中的相应记录
            strSql = "DELETE  FROM `post` WHERE `PostTeamID`='" + teamid + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除topic中的相应记录
            strSql = "DELETE  FROM `topic` WHERE `TopicTeamID`='" + teamid + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);

            return 1;                                                                                       //删除成功
        }

        /// <summary>
        /// 在数据库中注销该团队
        /// </summary>
        /// <param name="team">团队</param>
        /// <returns>删除结果：删除相应的团队记录，团队文件记录，团队新鲜事/话题记录，团队成员关系，返回1成功，0失败</returns>
        public int Delete(Team team)
        {
            String strSql = "DELETE  FROM `teaminfo` WHERE `TeamID`='" + team.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除reluserteam中的相应记录
            strSql = "DELETE  FROM `reluserteam` WHERE `TeamID`='" + team.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除teamfile中的相应记录
            strSql = "DELETE  FROM `teamfile` WHERE `TeamID`='" + team.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除post中的相应记录
            strSql = "DELETE  FROM `post` WHERE `PostTeamID`='" + team.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);
            //删除topic中的相应记录
            strSql = "DELETE  FROM `topic` WHERE `TopicTeamID`='" + team.ID + "'";
            MySqlHelper.ExecuteNonQuery(strConn, strSql, null);

            return 1;                                                                                       //删除成功
        }

        /// <summary>
        /// 在数据库中根据团队编号查询到特定团队
        /// </summary>
        /// <param name="ID">团队编号</param>
        /// <returns>查询结果：若查询到，则返回该团队；否则返回ID为-1的团队</returns>
        public Team GetTeamByID(int ID)
        {
            String strSql = "SELECT `TeamName`, `Description`, `AdminID` FROM `teaminfo` WHERE `TeamID`='" + ID + "'"; ;
            DataSet ds = DSMgn.ReadDataSet(strSql, strConn);
            if (ds.Tables[0].Rows.Count == 0)
            {
                Team team = new Team();
                team.ID = -1;
                return team;
            }                                                                                                    //不存在该名称的团队，查询失败
            else
            {
                Team team = new Team(ID, DSMgn.Readstr(strSql, 0, strConn), DSMgn.Readstr(strSql, 1, strConn), Convert.ToInt32(DSMgn.Readstr(strSql, 2, strConn)));
                return team;
            }                                                                                                    //查询成功
        }
    }
}